package es.ats.codington.festival.daos;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import es.ats.codington.festival.domain.LargeBusiness;
import es.ats.codington.festival.domain.Museum;
import es.ats.codington.festival.domain.Park;
import es.ats.codington.festival.domain.Stadium;
import es.ats.codington.festival.domain.Theater;
import es.ats.codington.festival.domain.TouristAttractions;
import es.ats.codington.festival.domain.TraditionalMarkets;

import es.ats.codington.festival.domain.Event;
import es.ats.codington.festival.domain.Zoo;
import es.ats.codington.festival.interfaces.daos.IEventDAOImp;
import es.ats.codington.festival.services.PlaceDAOService;

public class EventDAOImp implements IEventDAOImp{

	int nRows;

	public EventDAOImp(){
		
	}

	public Event getEventById(int eventId, String usTickets, Connection con){
		Event event = new Event();
		ResultSet resultSet = null;
		PreparedStatement statement = null;
		PlaceDAOService placeDAOService = new PlaceDAOService();
		
		try{
					
			statement = con.prepareStatement("select * from event where eventid = ?");
			statement.setInt(1, eventId);
			resultSet = statement.executeQuery();
			resultSet.next();
			
			event.setEventId(eventId);
			event.setEventName(resultSet.getString("eventname"));
			event.setEventDescription(resultSet.getString("eventdescription"));
			event.setDuration(resultSet.getString("duration"));
			event.setEventType(resultSet.getString("eventype"));
			event.setTickets(resultSet.getString("tickets"));
			event.setUsTickets(usTickets);
			event.setPlaces(placeDAOService.getPlaceByIdService(resultSet.getInt("place_placeid")));
			
			
		}catch (SQLException e){
			System.out.println(e.getMessage());
		}
		
		return event;
	}
	
	public Event getEventById(int eventId, Connection con){
		Event event = new Event();
		ResultSet resultSet = null;
		PreparedStatement statement = null;
		PlaceDAOService placeDAOService = new PlaceDAOService();
		
		try{
					
			statement = con.prepareStatement("select * from event where eventid = ?");
			statement.setInt(1, eventId);
			resultSet = statement.executeQuery();
			resultSet.next();
			
			event.setEventId(eventId);
			event.setEventName(resultSet.getString("eventname"));
			event.setEventDescription(resultSet.getString("eventdescription"));
			event.setDuration(resultSet.getString("duration"));
			event.setEventType(resultSet.getString("eventype"));
			event.setTickets(resultSet.getString("tickets"));
			event.setPlaces(placeDAOService.getPlaceByIdService(resultSet.getInt("place_placeid")));
			
			
		}catch (SQLException e){
			System.out.println(e.getMessage());
		}
		
		return event;
	}
	
	public ArrayList<Event> getEventList(Connection con){
		ArrayList<Event> listEvents = new ArrayList<Event>();
		PreparedStatement statement = null;
		PreparedStatement pstm = null;
		ResultSet resultSet = null;
		ResultSet rs = null;
		
		try{
			
			statement = con.prepareStatement("select * from Event");
			resultSet = statement.executeQuery();
			
			while (resultSet.next()){
				
				Event event = new Event();
				
				event.setEventId(resultSet.getInt("eventid"));
				event.setEventName(resultSet.getString("eventname"));
				event.setEventDescription(resultSet.getString("eventdescription"));
				event.setDuration(resultSet.getString("duration"));
				event.setEventType(resultSet.getString("eventype"));
				event.setTickets(resultSet.getString("tickets"));
				
				
				pstm = con.prepareStatement("select placename, capacity, placedescription, zone, placetype from place where placeid = ?");
				pstm.setInt(1,resultSet.getInt("place_placeid"));
				rs = pstm.executeQuery();
				rs.next();
						
				switch (rs.getInt("placetype")) {
				case LargeBusiness.placeType:
					LargeBusiness largeBusiness = new LargeBusiness(rs.getString("placename"),rs.getInt("capacity"),rs.getString("placedescription"),rs.getString("zone"));
					event.setPlaces(largeBusiness);
					break;
				case Museum.placeType:
					Museum museum = new Museum(rs.getString("placename"),rs.getInt("capacity"),rs.getString("placedescription"),rs.getString("zone"));
					event.setPlaces(museum);
					break;
				case Park.placeType:
					Park park = new Park(rs.getString("placename"),rs.getInt("capacity"),rs.getString("placedescription"),rs.getString("zone"));
					event.setPlaces(park);
					break;
				case Stadium.placeType:
					Stadium stadium = new Stadium(rs.getString("placename"),rs.getInt("capacity"),rs.getString("placedescription"),rs.getString("zone"));
					event.setPlaces(stadium);
					break;
				case Theater.placeType:
					Theater theater = new Theater(rs.getString("placename"),rs.getInt("capacity"),rs.getString("placedescription"),rs.getString("zone"));
					event.setPlaces(theater);
					break;
				case TouristAttractions.placeType:
					TouristAttractions touristAttractions = new TouristAttractions(rs.getString("placename"),rs.getInt("capacity"),rs.getString("placedescription"),rs.getString("zone"));
					event.setPlaces(touristAttractions);
					break;
				case TraditionalMarkets.placeType:
					TraditionalMarkets traditionalMarkets = new TraditionalMarkets(rs.getString("placename"),rs.getInt("capacity"),rs.getString("placedescription"),rs.getString("zone"));
					event.setPlaces(traditionalMarkets);
					break;
				case Zoo.placeType:
					Zoo zoo = new Zoo(rs.getString("placename"),rs.getInt("capacity"),rs.getString("placedescription"),rs.getString("zone"));
					event.setPlaces(zoo);
					break;
				default:
					break;
				}
				listEvents.add(event);
			}
			
		}catch(SQLException e){
			System.out.println(e.getMessage());
		}			
				
		return listEvents;
	}
	
	public int insertEvent(Event event, Connection con){
		nRows=0;
		try{
			PreparedStatement statement = con.prepareStatement("insert into event (eventname, eventdescription, duration, eventype, tickets, place_placeid) values(?,?,?,?,?,?);");
			statement.setString(1,event.getEventName());
			statement.setString(2,event.getEventDescription());
			statement.setString(3,event.getDuration());
			statement.setString(4,event.getEventType());
			statement.setString(5,event.getTickets());
			statement.setInt(6,event.getPlaces().getPlaceId());
				
			con.setAutoCommit(false);
			
			nRows = statement.executeUpdate();
			con.commit();
			
		}catch (SQLException e){
			System.out.println(e.getMessage());
		}
		return nRows;
	}
	
	public int deleteEvent(int eventId, Connection con){
		nRows = 0;
		
		try{
			PreparedStatement statement = con.prepareStatement("delete from Event where eventid = ?");
			statement.setInt(1, eventId);
			
			con.setAutoCommit(false);
			
			nRows = statement.executeUpdate();
			con.commit();
			
		}catch (SQLException e){
			System.out.println(e.getMessage());
		}
		return nRows;
	}
	
	public int updateEvent(Event uEvent, Connection con){
		nRows = 0;
		try{
			PreparedStatement statement = con.prepareStatement("update event set eventname = ?, eventdescription = ?, duration = ?, eventype = ?, tickets = ?, place_placeid = ? where eventid = ?");
			statement.setString(1,uEvent.getEventName());
			statement.setString(2,uEvent.getEventDescription());
			statement.setString(3,uEvent.getDuration());
			statement.setString(4,uEvent.getEventType());
			statement.setString(5,uEvent.getTickets());
			statement.setInt(6,uEvent.getPlaces().getPlaceId());
			statement.setInt(7,uEvent.getEventId());

			con.setAutoCommit(false);
			
			nRows = statement.executeUpdate();
			con.commit();
			
		}catch (SQLException e){
			System.out.println(e.getMessage());
		}
		return nRows;
	}

	
}